using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using OgilvyOne.Model;
using OgilvyOne.IDAL;
using System.Collections;
using OgilvyOne.Utility;

namespace OgilvyOne.SqlServerDAL
{
    public class DownLoad : IDownLoad
    {
        // Methods
        public void Delete(int download_id)
        {
            string cmdText = "UPDATE download_info SET isdel='1' WHERE DOWNLOAD_ID = " + download_id;
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public IList DropDownListBind()
        {
            string cmdText = "SELECT TYPE_ID,TYPE_NAME FROM download_type";
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    DownLoadType type = new DownLoadType(reader.GetInt32(0), reader.GetString(1));
                    list.Add(type);
                }
            }
            return list;
        }

        public IList DropDownListBindpicture(int menu_uid)
        {
            string cmdText = "SELECT TYPE_ID,TYPE_NAME FROM download_type where menu_uid=" + menu_uid;
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    DownLoadType type = new DownLoadType(reader.GetInt32(0), reader.GetString(1));
                    list.Add(type);
                }
            }
            return list;
        }

        public IList getAllConDownLoadList(int menu_uid)
        {
            string cmdText = "";
            cmdText = "SELECT a.DOWNLOAD_ID,a.DOWNLOAD_TYPE,a.DOWNLOAD_NAME,a.DOWNLOAD_DESC,a.DOWNLOAD_PATH,a.CREATE_DATE,a.MENU_UID";
            object obj2 = cmdText + " from download_info a inner join con_download b on a.download_id=b.download_id where";
            cmdText = string.Concat(new object[] { obj2, " b.con_id in(select con_id from con_info where menu_uid in (select menu_uid from dbo.GetChildCategories(", menu_uid, ")))" }) + " and a.isdel='0'";
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    DownLoadInfo info = new DownLoadInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetDateTime(5).ToShortDateString(), reader.GetInt32(6));
                    list.Add(info);
                }
            }
            return list;
        }

        public IList getconDownLoadList(int con_id)
        {
            string cmdText = "SELECT a.DOWNLOAD_ID,a.DOWNLOAD_TYPE,a.DOWNLOAD_NAME,a.DOWNLOAD_DESC,a.DOWNLOAD_PATH,a.CREATE_DATE,a.MENU_UID from download_info a inner join con_download b on a.download_id=b.download_id where b.con_id=" + con_id + " and a.isdel='0'";
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    DownLoadInfo info = new DownLoadInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetDateTime(5).ToShortDateString(), reader.GetInt32(6));
                    list.Add(info);
                }
            }
            return list;
        }

        public DownLoadInfo getDownLoadInfo(int download_ID)
        {
            string cmdText = "SELECT DOWNLOAD_ID,DOWNLOAD_TYPE,DOWNLOAD_NAME,DOWNLOAD_DESC,DOWNLOAD_PATH,CREATE_DATE,MENU_UID,IMAGE_WIDTH,IMAGE_HEIGHT FROM download_Info where DOWNLOAD_ID=" + download_ID + " and isdel='0'";
            DownLoadInfo info = null;
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    info = new DownLoadInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetDateTime(5).ToShortDateString(), reader.GetInt32(6));
                    info.IMAGE_WIDTH = reader.GetInt32(7);
                    info.IMAGE_HEIGHT = reader.GetInt32(8);
                }
            }
            return info;
        }

        public IList getDownLoadList(int MENU_UID)
        {
            string cmdText = "SELECT DOWNLOAD_ID,DOWNLOAD_TYPE,DOWNLOAD_NAME,DOWNLOAD_DESC,DOWNLOAD_PATH,CREATE_DATE,MENU_UID FROM download_Info where MENU_UID=" + MENU_UID + " and isdel='0'";
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    DownLoadInfo info = new DownLoadInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetDateTime(5).ToShortDateString(), reader.GetInt32(6));
                    list.Add(info);
                }
            }
            return list;
        }

        public void Insert(DownLoadInfo downloadinfo)
        {
            string cmdText = string.Concat(new object[] { "INSERT INTO download_info(DOWNLOAD_TYPE,DOWNLOAD_NAME,DOWNLOAD_DESC,DOWNLOAD_PATH,CREATE_DATE,IMAGE_WIDTH,IMAGE_HEIGHT,MENU_UID,isdel) VALUES(", downloadinfo.DownLoad_type, ",'", StringUtil.ReplaceStr(downloadinfo.DownLoad_name), "','", StringUtil.ReplaceStr(downloadinfo.DownLoad_desc), "','", downloadinfo.DownLoad_path, "',getdate(),", downloadinfo.IMAGE_WIDTH, ",", downloadinfo.IMAGE_HEIGHT, ",", downloadinfo.MENU_UID, ",'0')" });
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                }
            }
        }

        public void Update(DownLoadInfo downloadinfo)
        {
            string cmdText = string.Concat(new object[] { "UPDATE download_info SET DOWNLOAD_TYPE = ", downloadinfo.DownLoad_type, ",DOWNLOAD_NAME = '", StringUtil.ReplaceStr(downloadinfo.DownLoad_name), "',DOWNLOAD_DESC = '", StringUtil.ReplaceStr(downloadinfo.DownLoad_desc), "',DOWNLOAD_PATH = '", downloadinfo.DownLoad_path, "',IMAGE_WIDTH = ", downloadinfo.IMAGE_WIDTH, ",IMAGE_HEIGHT = ", downloadinfo.IMAGE_HEIGHT, "  WHERE DOWNLOAD_ID =", downloadinfo.DownLoad_ID });
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }
    }
}